import type { GetPostsOptionsFilter } from '../post/post.service'
import { connection } from '../app/database/mysql'
import { CommentModel } from './comment.model'
import { sqlFragment } from './comment.provider'

export const createComment = async (comment: CommentModel) => {
  const statement = `INSERT INTO comment SET ?`
  const [data] = await connection.promise().query(statement, [comment])
  return data
}

export const isReplyComment = async (commentId: number) => {
  const statement = `SELECT parent_id FROM comment WHERE id = ?`

  const [data] = await connection.promise().query(statement, [commentId])

  return !!data[0].parent_id
}

export const updateComment = async (comment: CommentModel) => {
  const { id, content } = comment
  const statement = `UPDATE comment SET content = ? WHERE id = ?`
  const [data] = await connection.promise().query(statement, [content, id])
  return data
}

export const deleteComment = async (commentId: number) => {
  const statement = `DELETE FROM comment WHERE id = ?`
  const [data] = await connection.promise().query(statement, [commentId])
  return data
}

interface GetCommentOptions {
  filter?: GetPostsOptionsFilter
  pagination?: { limit; offset }
}

export const getComments = async ({
  filter,
  pagination: { limit, offset }
}: GetCommentOptions) => {
  let params: Array<any> = [limit, offset]

  if (filter.param) {
    params = [filter.param, ...params]
  }

  const statement = `
      SELECT
        comment.id,
        comment.content,
        ${sqlFragment.user},
        ${sqlFragment.post}
        ${filter.name == 'userReplied' ? `,${sqlFragment.repliedComment}` : ''}
         ${filter.name !== 'userReplied' ? `,${sqlFragment.totalReplies}` : ''}
      FROM
        comment 
        ${sqlFragment.leftJoinUser}
        ${sqlFragment.leftJoinPost}
      WHERE
        ${filter.sql}
      GROUP BY
        comment.id 
      ORDER BY 
        comment.id DESC
      LIMIT ?
      OFFSET ?
  `

  const [data] = await connection.promise().query(statement, params)
  return data
}

export const getCommentsTotal = async (filter: GetPostsOptionsFilter) => {
  let params: Array<any> = []
  if (filter.param) {
    params = [filter.param, ...params]
  }
  const statement = `SELECT COUNT(DISTINCT comment.id) as total FROM comment ${sqlFragment.leftJoinUser} ${sqlFragment.leftJoinPost} WHERE ${filter.sql}`
  const [data] = await connection.promise().query(statement, params)
  return data[0].total
}

interface GetCommentRepliesOptions {
  commentId: number
}

export const getCommentReplies = async ({
  commentId
}: GetCommentRepliesOptions) => {
  const statement = `
    SELECT 
      comment.id,
      comment.content,
      ${sqlFragment.user} 
    FROM 
      comment 
      ${sqlFragment.leftJoinUser} 
    WHERE 
      comment.parent_id = ? 
    GROUP BY 
      comment.id
    `
  const [data] = await connection.promise().query(statement, [commentId])
  return data
}
